﻿-- =============================================
-- Author:		zengfei
-- Create date: 09m/09/2011
-- Description:	操作商品资料表
-- =============================================
CREATE PROCEDURE [dbo].[Usp_ModifyProduct]
	@p_Action	char,				-- A:新增, C:修改, D:删除
	@p_ProductNo	varchar(10),
	@p_ProductName	varchar(120),
	@p_ShortName	varchar(30),
	@p_ProductSpec	varchar(60),
	@p_ProductModel	varchar(60),
	@P_ProdBarcode	varchar(30),
	@P_ProdClass	int,
	@p_BaseUnit	int,
	@p_AidUnit	int,
	@p_BaseForAid	int,
	@p_Place	varchar(100),
	@p_Brand	varchar(100),
	@p_Bopomofo	varchar(30),
	@p_Remark	varchar(100),
	@p_RecordID	int out,
	@p_UserID	varchar(60),
	@p_ErrMsg	varchar(100) out,
	@p_RFlag	smallint out
AS
if	@p_Action = 'A'
begin
	if	exists (select * from Product where ProductNo = @p_ProductNo)
	begin
		raiserror('商品编号[%s]重复!', 16, 1, @p_ProductNo)
		goto ErrHandle
	end
	
	begin transaction
	
	insert into Product (ProductNo, ProductName, ShortName, ProductSpec, ProductModel, ProdBarcode, ProdClass, BaseUnit, AidUnit, BaseForAid, Place, Brand, Bopomofo, Remark)
		values (@p_ProductNo, @P_ProductName, @p_ShortName, @p_ProductSpec, @p_ProductModel, @P_ProdBarcode, @P_ProdClass, @p_BaseUnit, @p_AidUnit,@p_BaseForAid, @p_Place, @p_Brand, @p_Bopomofo, @p_Remark)
	
	if	@@ERROR <> 0 or @@ROWCOUNT = 0 goto ErrHandle
	
	select @p_RecordID = @@IDENTITY 
	
	insert into Product_log (ProductNo, ProductName, ShortName, ProductSpec, ProductModel, ProdBarcode, ProdClass, BaseUnit, AidUnit, BaseForAid, Place, Brand, Bopomofo, Remark, RecordID, UserID, ModifyTime, [Action])
		values (@p_ProductNo, @P_ProductName, @p_ShortName, @p_ProductSpec, @p_ProductModel, @P_ProdBarcode, @P_ProdClass, @p_BaseUnit, @p_AidUnit,@p_BaseForAid, @p_Place, @p_Brand, @p_Bopomofo, @p_Remark, @p_RecordID, @p_UserID, GETDATE(), @p_Action)
	
	if	@@ERROR <> 0 or @@ROWCOUNT = 0 goto ErrHandle
	
	commit transaction
end
else if	@p_Action = 'C'
begin
	begin transaction
	
	update Product set ProductName = @p_ProductName, 
						ShortName = @p_ShortName,
						ProductSpec = @p_ProductSpec,
						ProductModel = @p_ProductModel,
						ProdBarcode = @P_ProdBarcode,
						ProdClass = @P_ProdClass, 
						BaseUnit = @p_BaseUnit,
						AidUnit = @p_AidUnit, 
						BaseForAid = @p_BaseForAid, 
						Place = @p_Place, 
						Brand = @p_Brand, 
						Bopomofo = @p_Bopomofo, 
						Remark = @p_Remark 
					where RecordID = @p_RecordID 
	
	if	@@ERROR <> 0 or @@ROWCOUNT = 0 goto ErrHandle
	
	insert into Product_log (ProductNo, ProductName, ShortName, ProductSpec, ProductModel, ProdBarcode, ProdClass, BaseUnit, AidUnit, BaseForAid, Place, Brand, Bopomofo, Remark, RecordID, UserID, ModifyTime, [Action])
		values (@p_ProductNo, @P_ProductName, @p_ShortName, @p_ProductSpec, @p_ProductModel, @P_ProdBarcode, @P_ProdClass, @p_BaseUnit, @p_AidUnit,@p_BaseForAid, @p_Place, @p_Brand, @p_Bopomofo, @p_Remark, @p_RecordID, @p_UserID, GETDATE(), @p_Action)
	
	if	@@ERROR <> 0 or @@ROWCOUNT = 0 goto ErrHandle
	
	commit transaction
end
else if	@p_Action = 'D'
begin
	begin transaction
	
	Delete from Product where RecordID = @p_RecordID 
	
	if	@@ERROR <> 0 or @@ROWCOUNT = 0 goto ErrHandle
	
	insert into Product_log (ProductNo, ProductName, ShortName, ProductSpec, ProductModel, ProdBarcode, ProdClass, BaseUnit, AidUnit, BaseForAid, Place, Brand, Bopomofo, Remark, RecordID, UserID, ModifyTime, [Action])
		values (@p_ProductNo, @P_ProductName, @p_ShortName, @p_ProductSpec, @p_ProductModel, @P_ProdBarcode, @P_ProdClass, @p_BaseUnit, @p_AidUnit,@p_BaseForAid, @p_Place, @p_Brand, @p_Bopomofo, @p_Remark, @p_RecordID, @p_UserID, GETDATE(), @p_Action)
	
	if	@@ERROR <> 0 or @@ROWCOUNT = 0 goto ErrHandle
	
	commit transaction
end

set @p_RFlag = 1


ErrHandle:

if	@@TRANCOUNT > 0	rollback transaction

set @p_RFlag = -1
